package study.itwill.team5.house;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import study.itwill.common.DBHandler;
import study.itwill.common.DateTime;
import study.itwill.jsp.MultipartBox;
import study.itwill.jsp.MultipartBox.FileInfo;

/**
 * 민박 - 추가, 수정, 삭제, 조회, 리스트(검색포함) 기능을 가진 클래스
 */
public class House {
	
	// Logger 클래스의 인스턴스를 받아온다.
	static Logger logger = Logger.getLogger(House.class);
		
	private static final String TABLE = "house";
	
	// 데이터베이스 제어 객체에 대한 참조
	DBHandler db = null;
	
	/** 현재시간 데이터 저장을 위한 SQL구문 작성 */
	String datetime = DateTime.getInstance().getNowDateTimeString();
	
	public House() {
		// DB객체에 대한 참조 연결
		db = DBHandler.getInstance();
	}

	/**
	 * 민박 추가 기능(관리자)
	 * 
	 * @param name				- 민박 상호명
	 * @param house_city_seq	- 민박 도시의 일련번호
	 * @param address1			- 전화번호 ( 국가 )
	 * @param address2			- 전화번호 ( 앞자리 )
	 * @param address3			- 전화번호 ( 가운데 )
	 * @param address4			- 전화번호 ( 끝자리 )
	 * @param email				- 민박 이메일
	 * @param homepage			- 민박 홈페이지
	 * @param info				- 민박 상세정보
	 * @param tel				- 연락처 배열
	 * @param imgae				- 이미지 배열
	 * @param room				- 룸정보 객체배열
	 * @return	int				- 추가된 회원의 일련번호
	 */
	public int addHouse(String name, int house_city_seq, String address1, String address2, 
						String address3, String address4, String email, String homepage, 
						String info, double lat, double lon, String[] tel, ArrayList<FileInfo> imageList, ArrayList<RoomItem> room) {
		//-----------------------------------------
		// 메서드 시작
		//-----------------------------------------
		logger.debug("=============== method start >> addHouse");
		logger.debug("[PARAMS] name=" + name);
		logger.debug("[PARAMS] house_city_seq=" + house_city_seq);
		logger.debug("[PARAMS] address1=" + address1);
		logger.debug("[PARAMS] address2=" + address2);
		logger.debug("[PARAMS] address3=" + address3);
		logger.debug("[PARAMS] address4=" + address4);
		logger.debug("[PARAMS] email=" + email);
		logger.debug("[PARAMS] homepage=" + homepage);
		logger.debug("[PARAMS] info=" + info);
		logger.debug("[PARAMS] lat=" + lat);
		logger.debug("[PARAMS] lon=" + lon);
		for (int i=0; i<tel.length; i++) {
			logger.debug("[PARAMS] tel=" + tel[i]);
		}
		logger.debug("[PARAMS] imageList=" + imageList.toString());
		logger.debug("[PARAMS] room=" + room.toString());
		
		int seq = -1;
		
		// SQL 인젝션 방어
		name = name.replace("'", "\'");
		address1 = address1.replace("'", "\'");
		address2 = address2.replace("'", "\'");
		address3 = address3.replace("'", "\'");
		address4 = address4.replace("'", "\'");
		email = email.replace("'", "\'");
		homepage = homepage.replace("'", "\'");
		info = info.replace("'", "\'");
		for (int i=0; i<tel.length; i++) {
			tel[i] = tel[i].replace("'", "\'");
		}
		for (int i=0; i<room.size(); i++) {
			RoomItem item = room.get(i);
			String temp = item.getName();
			temp = temp.replace("'", "\'");
		}
		
		// 트랜젝션에 대한 판단이 잘 서지 않는다면, 무조건 하면 된다.
		db.beginTrans();
		
		// INSERT 처리를 위한 구현
		String sql1 = "INSERT INTO house("
	            + "name, address1, address2, address3, address4, email, homepage, info, lat, lon, reg_date, edit_date, house_city_seq"
	            + ") VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', %f, %f, '%s', '%s', %d);";
		String sql2 = "INSERT INTO house_tel("
				+ "tel, reg_date, edit_date, house_seq"
				+ ") VALUES ('%s', '%s', '%s', %d);";
		String sql4 = "INSERT INTO house_room("
				+ "name, limit_person, off_price, on_price, reg_date, edit_date, house_seq"
				+ ") VALUES ('%s', %d, %d, %d, '%s', '%s', %d);";
		
		
		boolean result = db.execSql(sql1, name, address1, address2, address3, address4, email, 
				homepage, info, lat, lon, datetime, datetime, house_city_seq);
		
		seq = db.getMax(TABLE, "seq", null);
		
		/** (3) 기본 데이터를 참조하는 파일 목록에 대한 SQL 구문 작성 */
		String fileSql = "INSERT INTO house_image (";
		fileSql += "fileOrgName, fileSaveName, fileDir, fileSize, fileType, ";
		fileSql += "reg_date, edit_date, house_seq";
		fileSql += ") VALUES (";
		fileSql += "'%s', '%s', '%s', %d, '%s', '%s', '%s', %d";
		fileSql += ")";
		
		// ArrayList에 저장된 파일 데이터의 수 만큼 반복
		for (int i=0; i<imageList.size(); i++) {
			FileInfo item = imageList.get(i);
			boolean fileOk = db.execSql(fileSql, item.getFileOrgName(), item.getFileSaveName(), item.getFileDir(), 
					item.getFileSize(), item.getFileType(), datetime, datetime, seq);
			
			if (!fileOk) {
				// 모든 데이터 저장 취소
				db.rollback();
				return -1;
			}
		}
		
		for (int i=0; i<tel.length; i++) {
			result = db.execSql(sql2, tel[i], datetime, datetime, seq);
		}
		for (int i=0; i<room.size(); i++) {
			RoomItem item = room.get(i);
			result = db.execSql(sql4, item.getName(), item.getLimit_person(), item.getOff_price(), item.getOn_price(), datetime, datetime, seq);
		}
		
		//-----------------------------------------
		// 결과처리
		//-----------------------------------------
		if(result) {
			db.commit();
			logger.debug("처리결과 >> " + seq + "번 민박 추가 성공");
		} else {
			db.rollback();
			logger.error("처리결과 >> 민박 추가 실패");
		}
		
		logger.debug("=============== method end >> addHouse");
		
		
		return seq;
	}
	
	/**
	 * 민박 수정 기능(관리자)
	 * 
	 * @param seq				- 민박 일련번호
	 * @param name				- 민박 상호명
	 * @param house_city_seq	- 민박 도시의 일련번호
	 * @param address1			- 전화번호 ( 국가 )
	 * @param address2			- 전화번호 ( 앞자리 )
	 * @param address3			- 전화번호 ( 가운데 )
	 * @param address4			- 전화번호 ( 끝자리 )
	 * @param email				- 민박 이메일
	 * @param homepage			- 민박 홈페이지
	 * @param info				- 민박 상세정보
	 * @param tel				- 연락처 배열
	 * @param imgae				- 이미지 배열
	 * @param room				- 룸정보 객체배열
	 * @return boolean			- 수정 성공 - true, 수정 실패 - false
	 */
	public boolean editHouse(int seq, String name, int house_city_seq, String address1, String address2, 
			String address3, String address4, String email, String homepage, String info, double lat, double lon, String[] tel, 
			ArrayList<FileInfo> imageList, ArrayList<RoomItem> room, String[] delete_file) {
		// -----------------------------------------
		// 메서드 시작
		// -----------------------------------------
		logger.debug("=============== method start >> editHouse");
		logger.debug("[PARAMS] seq=" + seq);
		logger.debug("[PARAMS] name=" + name);
		logger.debug("[PARAMS] house_city_seq=" + house_city_seq);
		logger.debug("[PARAMS] address1=" + address1);
		logger.debug("[PARAMS] address2=" + address2);
		logger.debug("[PARAMS] address3=" + address3);
		logger.debug("[PARAMS] address4=" + address4);
		logger.debug("[PARAMS] email=" + email);
		logger.debug("[PARAMS] homepage=" + homepage);
		logger.debug("[PARAMS] info=" + info);
		logger.debug("[PARAMS] lat=" + lat);
		logger.debug("[PARAMS] lon=" + lon);
		for (int i=0; i<tel.length; i++) {
			logger.debug("[PARAMS] tel=" + tel[i]);
		}
		logger.debug("[PARAMS] imageList=" + imageList.toString());
		logger.debug("[PARAMS] room=" + room.toString());

		// SQL 인젝션 방어
		name = name.replace("'", "\'");
		address1 = address1.replace("'", "\'");
		address2 = address2.replace("'", "\'");
		address3 = address3.replace("'", "\'");
		address4 = address4.replace("'", "\'");
		email = email.replace("'", "\'");
		homepage = homepage.replace("'", "\'");
		info = info.replace("'", "\'");
		for (int i = 0; i < tel.length; i++) {
			tel[i] = tel[i].replace("'", "\'");
		}
		for (int i = 0; i < room.size(); i++) {
			RoomItem item = room.get(i);
			String temp = item.getName();
			temp = temp.replace("'", "\'");
		}

		boolean result = false;

		db.beginTrans();
		
		// (1) 삭제할 파일에 대한 리스트를 불러온다.
		ArrayList<FileInfo> delete_file_list = null;
		
		if (delete_file != null) {
			String in_cause = "(";
			
			for (int i=0; i<delete_file.length; i++) {
				in_cause += "'" + delete_file[i] + "'";
				
				if (i + 1 < delete_file.length) {
					in_cause += ",";
				}
			}
			
			in_cause += ")";
			
			// 삭제 대상을 불러올 SQL
			String fileSql = "SELECT fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house_image "
					+ "WHERE house_seq=%d and fileSaveName in %s";
			ResultSet fileRs = db.getResult(fileSql, seq, in_cause);
			
			// 삭제 대상을 만들기 위한 ArrayList 작성
			if (fileRs != null) {
				try {
					delete_file_list = new ArrayList<FileInfo>();
					
					while(fileRs.next()) {
						String fileOrgName = fileRs.getString(1);
						String fileSaveName = fileRs.getString(2);
						String fileDir = fileRs.getString(3);
						int fileSize = fileRs.getInt(4);
						String fileType = fileRs.getString(5);
						
						FileInfo file_info = new FileInfo("", fileOrgName, fileSaveName, fileDir, fileSize, fileType);
						logger.debug("delete_fileInfo : " + file_info.toString());
						delete_file_list.add(file_info);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					if (fileRs != null) {
						try {
							fileRs.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
						fileRs = null;
					}
				}
				
				// 게시물 데이터 삭제처리
				String delSql = "DELETE FROM house_image "
						+ "WHERE house_seq=%d and fileSaveName in %s";
				db.execSql(delSql, seq, in_cause);
			}
		}
		
		
		// (2) 삭제 대상에 대한 처리
		if (delete_file_list != null) {
			MultipartBox mbox = MultipartBox.getInstance();
			mbox.deleteFiles(delete_file_list);
		}

		// (3) 게시물 정보 갱신
		String sql1 = "UPDATE house "
				+ "SET name = '%s', address1 = '%s', address2 = '%s', address3 = '%s', address4 = '%s', email = '%s', homepage = '%s', info = '%s', lat = %f, lon = %f, edit_date = '%s', house_city_seq = %d "
				+ "WHERE seq = %d;";
		String sql2 = "DELETE FROM house_tel "
				+ "WHERE house_seq = %d;";
		String sql4 = "DELETE FROM house_room "
				+ "WHERE house_seq = %d;";

		result = db.execSql(sql1, name, address1, address2, address3, address4, 
				email, homepage, info, lat, lon, datetime, house_city_seq, seq);
		result = db.execSql(sql2, seq);
		result = db.execSql(sql4, seq);
		
		sql2 = "INSERT INTO house_tel("
				+ "tel, reg_date, edit_date, house_seq"
				+ ") VALUES ('%s', '%s', '%s', %d);";
		sql4 = "INSERT INTO house_room("
				+ "name, limit_person, off_price, on_price, reg_date, edit_date, house_seq"
				+ ") VALUES ('%s', %d, %d, %d, '%s', '%s', %d);";

		for (int i=0; i<tel.length; i++) {
			result = db.execSql(sql2, tel[i], datetime, datetime, seq);
		}
		for (int i=0; i<room.size(); i++) {
			RoomItem item = room.get(i);
			result = db.execSql(sql4, item.getName(), item.getLimit_person(), item.getOff_price(), item.getOn_price(), datetime, datetime, seq);
		}
		
		// (4) 새롭게 업로드 된 파일 정보 처리
		/** 기본 데이터를 참조하는 파일 목록에 대한 SQL 구문 작성 */
		String fileSql = "INSERT INTO house_image (";
		fileSql += "fileOrgName, fileSaveName, fileDir, fileSize, fileType, ";
		fileSql += "reg_date, edit_date, house_seq";
		fileSql += ") VALUES (";
		fileSql += "'%s', '%s', '%s', %d, '%s', '%s', '%s', %d";
		fileSql += ")";
		
		// ArrayList에 저장된 파일 데이터의 수 만큼 반복
		for (int i=0; i<imageList.size(); i++) {
			FileInfo item = imageList.get(i);
			db.execSql(fileSql, item.getFileOrgName(), item.getFileSaveName(), item.getFileDir(), 
					item.getFileSize(), item.getFileType(), datetime, datetime, seq);
		}
		result = true;

		if(result) {
			db.commit();
			logger.debug("처리결과 >> " + seq + "번 민박정보 수정성공");
		} else {
			db.rollback();
			logger.error("처리결과 >> 회원정보 수정 실패 ");
		}

		logger.debug("=============== method end >> editHouse");

		return result;
	}

	/**
	 * 민박 삭제 기능 (관리자)
	 * @param seq			- 민박 일련 번호
	 * @return boolean		- 삭제 성공 - true, 삭제 실패 - false
	 */
	public boolean deleteHouse(int seq) {
		logger.debug("=============== method start >> deleteHouse");
		logger.debug("[PARAMS] seq=" + seq);
		
		boolean result = false;
		
		// 파일업로드 데이터 가져오기
		ArrayList<FileInfo> list = this.getImageList(seq);
		
		db.beginTrans();
		
		String sql = "DELETE FROM house_tel WHERE house_seq = %d;";
		result = db.execSql(sql, seq);
		
		sql = "DELETE FROM house_image WHERE house_seq = %d;";
		result = db.execSql(sql, seq);
		
		sql = "DELETE FROM house_room WHERE house_seq = %d;";
		result = db.execSql(sql, seq);
		
		sql = "DELETE FROM wishlist WHERE house_seq = %d;";
		result = db.execSql(sql, seq);
		
		sql = "DELETE FROM board WHERE house_seq = %d;";
		result = db.execSql(sql, seq);
		
		sql = "DELETE FROM house WHERE seq = %d;";
		result = db.execSql(sql, seq);
		
		if(result) {
			db.commit();
			// 모든 쿼리가 성공이라면 파일삭제
			MultipartBox mbox = MultipartBox.getInstance();
			mbox.deleteFiles(list);
			logger.debug("처리결과 >> " + seq + "번 민박 삭제 성공");
		} else {
			db.rollback();
			logger.error("처리결과 >> 민박 삭제 실패 ");
		}
		
		logger.debug("=============== method end >> deleteHouse");
		
		return result;
	}
	
	/**
	 * 민박 조회 기능
	 * @param seq			- 민박 테이블 일련번호
	 * @return HouseItem	- 민박 한개의 정보를 객체로 리턴
	 */
	public HouseItem viewHouse(int seq) {
		logger.debug("=============== method start >> viewHouse");
		logger.debug("[PARAMS] seq=" + seq);
		
		HouseItem item = null;
		
		// 데이터 조회
		String sql = "SELECT house.seq, house_city.city_name, name, address1, address2, address3, address4, email, homepage, info, grade, grade_count, house.reg_date, house.edit_date, house_city_seq, lat, lon, "
				+ "house.reg_date, house.edit_date, house_city_seq, city_name "
				+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
				+ "WHERE house.seq = %d;";
		
		ResultSet rs = db.getResult(sql, seq);
		
		if(rs != null) {
			// 데이터 조회 결과 있는 경우
			try {
				rs.next();
				
				// 1) ResultSet의 모든 데이터를 변수로 추출
				int house_seq = rs.getInt(1);
				String city_name = rs.getString(2);
				String name = rs.getString(3);
				String address1 = rs.getString(4);
				String address2 = rs.getString(5);
				String address3 = rs.getString(6);
				String address4 = rs.getString(7);
				String email = rs.getString(8);
				String homepage = rs.getString(9);
				String info = rs.getString(10);
				double grade = rs.getDouble(11);
				int grade_count = rs.getInt(12);
				String reg_date = rs.getString(13);
				String edit_date = rs.getString(14);
				int house_city_seq = rs.getInt(15);
				double lat = rs.getDouble(16);
				double lon = rs.getDouble(17);
				
				ArrayList<FileInfo> imageList = this.getImageList(seq);
				
				// 2) 추출된 값을 객체로 구성
				item = new HouseItem(house_seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, reg_date, edit_date, house_city_seq, city_name, imageList);
				
			} catch (SQLException e) {
				logger.error(e);
			} finally {
				// 3) 데이터 조회에 대한 메모리 반납
				try {
					rs.close();
				} catch (SQLException e) {}
				rs = null;
			}
		}
		
		if(item == null) {
			logger.error("데이터 조회 실패");
		} else {
			logger.debug("데이터 조회 성공");
			logger.debug(item.toString());
		}
				
		logger.debug("=============== method end >> viewHouse");
		
		return item;
	}
	
	/**
	 * 민박 연락처 조회 기능
	 * @param seq			- 민박 테이블 일련번호
	 * @return TelItem		- 민박 연락처 정보를 객체배열로 리턴
	 */
	public ArrayList<TelItem> viewTel(int seq) {
		logger.debug("=============== method start >> viewTel");
		logger.debug("[PARAMS] seq=" + seq);
		
		ArrayList<TelItem> list = null;
		
		// 데이터 조회
		String sql = "SELECT house_tel.seq, tel, house_tel.reg_date, house_tel.edit_date, house_seq, name "
				+ "FROM house_tel INNER JOIN house ON house_seq = house.seq "
				+ "WHERE house.seq = %d;";
		
		ResultSet rs = db.getResult(sql, seq);
		
		if(rs != null) {
			// 데이터 조회 결과 있는 경우
			try {
				list = new ArrayList<TelItem>();
				
				while(rs.next()) {
					
					// 1) ResultSet의 모든 데이터를 변수로 추출
					int house_tel_seq = rs.getInt(1);
					String tel = rs.getString(2);
					String reg_date = rs.getString(3);
					String edit_date = rs.getString(4);
					int house_seq = rs.getInt(5);
					String name = rs.getString(6);
					
					// 2) 추출된 값을 객체로 구성
					TelItem item = new TelItem(house_tel_seq, tel, reg_date, edit_date, house_seq, name);
					
					// 3) ArrayList에 추가
					list.add(item);
				}
			} catch (SQLException e) {
				logger.error(e);
			} finally {
				// 4) 데이터 조회에 대한 메모리 반납
				try {
					rs.close();
				} catch (SQLException e) {}
				rs = null;
			}
		}
		
		if(list == null) {
			logger.error("데이터 조회 실패");
		} else {
			logger.debug("데이터 조회 성공 >> " + list.size() + "건 조회됨");
		}
		
		logger.debug("=============== method end >> viewTel");
		
		return list;
	}
	
	
	
	/**
	 * 민박 룸 조회 기능
	 * @param seq			- 민박 테이블 일련번호
	 * @return ImageItem	- 민박 룸 정보를 객체배열로 리턴
	 */
	public ArrayList<RoomItem> viewRoom(int seq) {
		logger.debug("=============== method start >> viewRoom");
		logger.debug("[PARAMS] seq=" + seq);
		
		ArrayList<RoomItem> list = null;
		
		// 데이터 조회
		String sql = "SELECT house_room.name, limit_person, off_price, on_price "
				+ "FROM house_room INNER JOIN house ON house_seq = house.seq "
				+ "WHERE house.seq = %d;";
		
		ResultSet rs = db.getResult(sql, seq);
		
		if(rs != null) {
			// 데이터 조회 결과 있는 경우
			try {
				list = new ArrayList<RoomItem>();
				
				while(rs.next()) {
					
					// 1) ResultSet의 모든 데이터를 변수로 추출
					String name = rs.getString(1);
					int limit_person = rs.getInt(2);
					int off_price = rs.getInt(3);
					int on_price = rs.getInt(4);
					
					// 2) 추출된 값을 객체로 구성
					RoomItem item = new RoomItem(name, limit_person, off_price, on_price);
					
					// 3) ArrayList에 추가
					list.add(item);
				}
			} catch (SQLException e) {
				logger.error(e);
			} finally {
				// 4) 데이터 조회에 대한 메모리 반납
				try {
					rs.close();
				} catch (SQLException e) {}
				rs = null;
			}
		}
		
		if(list == null) {
			logger.error("데이터 조회 실패");
		} else {
			logger.debug("데이터 조회 성공 >> " + list.size() + "건 조회됨");
		}
		
		logger.debug("=============== method end >> viewRoom");
		
		return list;
	}

	/**
	 * 민박 리스트 조회를 위한 기능
	 * @param choice				- 검색조건 (0:민박전체, 1:도시 일련번호, 2:이름, 3:나라, 4:도시, 5:상세주소, 6:간단주소, 7:이메일, 8:홈페이지, 9:상세정보)
	 * @param search				- 검색어
	 * @return ArrayList<HouseItem>	- 게시글 리스트 정보를 객체배열로 리턴
	 */
	public ArrayList<HouseItem> listHouse(int choice, String search) {
		logger.debug("=============== method start >> listHouse");
		logger.debug("[PARAMS] choice=" + choice);
		logger.debug("[PARAMS] search=" + search);
		
		ArrayList<HouseItem> list = null;
		
		String sql = null;
		
		/*
		 *  민박 검색을 위한 파라미터 정의
		 *  0, null : 민박 전체리스트 검색
		 *  		(전체리스트 검색은 오버로딩 정의로 숫자값 하나만을 파라미터로 받을수 있다)
		 *  1, 도시 일련번호 검색
		 *  2, "민박이름 검색"
		 *  3, "나라이름 검색"
		 *  4, "도시이름 검색"
		 *  5, "상세주소 검색"
		 *  6, "간단주소 검색"
		 *  7, "이메일 검색"
		 *  8, "홈페이지 검색"
		 *  9, "상세정보 검색"
		 */
		switch(choice) {
		case 0:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "GROUP BY house.seq ORDER BY house.seq DESC;";
			break;
			
		case 1:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE house_city_seq = " + search + " GROUP BY house.seq ORDER BY house.seq DESC;";
			break;
			
		case 2:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE name like '%%" + search + "%%' GROUP BY house.seq ORDER BY house.seq DESC;";
			break;
			
		case 3:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE address1 like '%%" + search + "%%' GROUP BY house.seq ORDER BY house.seq DESC;";
			break;
			
		case 4:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE address2 like '%%" + search + "%%' GROUP BY house.seq ORDER BY house.seq DESC;";
			break;
			
		case 5:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE address3 like '%%" + search + "%%' GROUP BY house.seq ORDER BY house.seq DESC;";
			break;
			
		case 6:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE address4 like '%%" + search + "%%' GROUP BY house.seq ORDER BY house.seq DESC;";
			break;
			
		case 7:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE email like '%%" + search + "%%' GROUP BY house.seq ORDER BY house.seq DESC;";
			break;
			
		case 8:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE homepage like '%%" + search + "%%' GROUP BY house.seq ORDER BY house.seq DESC;";
			break;
			
		case 9:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE info like '%%" + search + "%%' GROUP BY house.seq ORDER BY house.seq DESC;";
			break;
			
		default:
			System.out.println("검색조건 선택이 올바르지 않습니다.");
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE seq=-1 GROUP BY house.seq ORDER BY house.seq DESC;";
			break;
		}
			
		
		ResultSet rs = db.getResult(sql);
		
		if(rs != null) {
			// 데이터 조회 결과 있는 경우
			try {
				list = new ArrayList<HouseItem>();
				
				while(rs.next()) {
					
					// 1) ResultSet의 모든 데이터를 변수로 추출
					int house_seq = rs.getInt(1);
					String name = rs.getString(2);
					String address1 = rs.getString(3);
					String address2 = rs.getString(4);
					String address3 = rs.getString(5);
					String address4 = rs.getString(6);
					String email = rs.getString(7);
					String homepage = rs.getString(8);
					double lat = rs.getDouble(9);
					double lon = rs.getDouble(10);
					double grade = rs.getDouble(11);
					int grade_count = rs.getInt(12);
					String info = rs.getString(13);
					String reg_date = rs.getString(14);
					String edit_date = rs.getString(15);
					int house_city_seq = rs.getInt(16);
					String city_name = rs.getString(17);
					String fileOrgName = rs.getString(18);
					String fileSaveName = rs.getString(19);
					String fileDir = rs.getString(20);
					int fileSize = rs.getInt(21);
					String fileType = rs.getString(22);
					
					ArrayList<FileInfo> imageList = new ArrayList<FileInfo>();
					imageList.add(new FileInfo("", fileOrgName, fileSaveName, fileDir, fileSize, fileType));
					
					// 2) 추출된 값을 객체로 구성
					HouseItem item = new HouseItem(house_seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, reg_date, edit_date, house_city_seq, city_name, imageList);
					
					// 3) ArrayList에 추가
					list.add(item);
				}
			} catch (SQLException e) {
				logger.error(e);
			} finally {
				// 4) 데이터 조회에 대한 메모리 반납
				try {
					rs.close();
				} catch (SQLException e) {}
				rs = null;
			}
		}
		
		if(list == null) {
			logger.error("데이터 조회 실패");
		} else {
			logger.debug("데이터 조회 성공 >> " + list.size() + "건 조회됨");
		}
		
		logger.debug("=============== method end >> listHouse");
		
		return list;
	}
	
	/**
	 * 민박 리스트 조회를 위한 기능
	 * @param choice				- 검색조건 (0:민박전체, 1:도시 일련번호, 2:이름, 3:나라, 4:도시, 5:상세주소, 6:간단주소, 7:이메일, 8:홈페이지, 9:상세정보)
	 * @param search				- 검색어
	 * @param start					- 검색 범위의 시작위치
	 * @param end					- 한 페이지에 보여질 글의 목록 수
	 * @return ArrayList<HouseItem>	- 게시글 리스트 정보를 객체배열로 리턴
	 */
	public ArrayList<HouseItem> listHouse(int choice, String search, int start, int end) {
		logger.debug("=============== method start >> listHouse");
		logger.debug("[PARAMS] choice=" + choice);
		logger.debug("[PARAMS] search=" + search);
		logger.debug("[PARAMS] start=" + start);
		logger.debug("[PARAMS] end=" + end);
		
		ArrayList<HouseItem> list = null;
		
		String sql = null;
		
		/*
		 *  민박 검색을 위한 파라미터 정의
		 *  0, null : 민박 전체리스트 검색
		 *  		(전체리스트 검색은 오버로딩 정의로 숫자값 하나만을 파라미터로 받을수 있다)
		 *  1, 도시 일련번호 검색
		 *  2, "민박이름 검색"
		 *  3, "나라이름 검색"
		 *  4, "도시이름 검색"
		 *  5, "상세주소 검색"
		 *  6, "간단주소 검색"
		 *  7, "이메일 검색"
		 *  8, "홈페이지 검색"
		 *  9, "상세정보 검색"
		 */
		switch(choice) {
		case 0:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "GROUP BY house.seq ORDER BY house.seq DESC LIMIT %d, %d;";
			break;
			
		case 1:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE house_city_seq = " + search + " GROUP BY house.seq ORDER BY house.seq DESC LIMIT %d, %d;";
			break;
			
		case 2:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE name like '%%" + search + "%%' GROUP BY house.seq ORDER BY house.seq DESC LIMIT %d, %d;";
			break;
			
		case 3:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE address1 like '%%" + search + "%%' GROUP BY house.seq ORDER BY house.seq DESC LIMIT %d, %d;";
			break;
			
		case 4:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE address2 like '%%" + search + "%%' GROUP BY house.seq ORDER BY house.seq DESC LIMIT %d, %d;";
			break;
			
		case 5:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE address3 like '%%" + search + "%%' GROUP BY house.seq ORDER BY house.seq DESC LIMIT %d, %d;";
			break;
			
		case 6:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE address4 like '%%" + search + "%%' GROUP BY house.seq ORDER BY house.seq DESC LIMIT %d, %d;";
			break;
			
		case 7:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE email like '%%" + search + "%%' GROUP BY house.seq ORDER BY house.seq DESC LIMIT %d, %d;";
			break;
			
		case 8:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE homepage like '%%" + search + "%%' GROUP BY house.seq ORDER BY house.seq DESC LIMIT %d, %d;";
			break;
			
		case 9:
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE info like '%%" + search + "%%' GROUP BY house.seq ORDER BY house.seq DESC LIMIT %d, %d;";
			break;
			
		default:
			System.out.println("검색조건 선택이 올바르지 않습니다.");
			sql = "SELECT house.seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, "
					+ "house.reg_date, house.edit_date, house_city_seq, city_name, fileOrgName, fileSaveName, fileDir, fileSize, fileType "
					+ "FROM house INNER JOIN house_city ON house_city_seq = house_city.seq "
					+ "INNER JOIN house_image ON house.seq = house_image.house_seq "
					+ "WHERE seq=-1 GROUP BY house.seq ORDER BY house.seq DESC LIMIT %d, %d;";
			break;
		}
			
		
		ResultSet rs = db.getResult(sql, start, end);
		
		if(rs != null) {
			// 데이터 조회 결과 있는 경우
			try {
				list = new ArrayList<HouseItem>();
				
				while(rs.next()) {
					
					// 1) ResultSet의 모든 데이터를 변수로 추출
					int house_seq = rs.getInt(1);
					String name = rs.getString(2);
					String address1 = rs.getString(3);
					String address2 = rs.getString(4);
					String address3 = rs.getString(5);
					String address4 = rs.getString(6);
					String email = rs.getString(7);
					String homepage = rs.getString(8);
					double lat = rs.getDouble(9);
					double lon = rs.getDouble(10);
					double grade = rs.getDouble(11);
					int grade_count = rs.getInt(12);
					String info = rs.getString(13);
					String reg_date = rs.getString(14);
					String edit_date = rs.getString(15);
					int house_city_seq = rs.getInt(16);
					String city_name = rs.getString(17);
					String fileOrgName = rs.getString(18);
					String fileSaveName = rs.getString(19);
					String fileDir = rs.getString(20);
					int fileSize = rs.getInt(21);
					String fileType = rs.getString(22);
					
					ArrayList<FileInfo> imageList = new ArrayList<FileInfo>();
					imageList.add(new FileInfo("", fileOrgName, fileSaveName, fileDir, fileSize, fileType));
					
					// 2) 추출된 값을 객체로 구성
					HouseItem item = new HouseItem(house_seq, name, address1, address2, address3, address4, email, homepage, lat, lon, grade, grade_count, info, reg_date, edit_date, house_city_seq, city_name, imageList);
					
					// 3) ArrayList에 추가
					list.add(item);
				}
			} catch (SQLException e) {
				logger.error(e);
			} finally {
				// 4) 데이터 조회에 대한 메모리 반납
				try {
					rs.close();
				} catch (SQLException e) {}
				rs = null;
			}
		}
		
		if(list == null) {
			logger.error("데이터 조회 실패");
		} else {
			logger.debug("데이터 조회 성공 >> " + list.size() + "건 조회됨");
		}
		
		logger.debug("=============== method end >> listHouse");
		
		return list;
	}
	
	public ArrayList<HouseItem> listHouse(int choice) {
		return listHouse(choice, null);
	}
	
	public ArrayList<HouseItem> listHouse(int choice, int start, int end) {
		return listHouse(choice, null, start, end);
	}
	
	private ArrayList<FileInfo> getImageList(int seq) {
		String fileSql = "SELECT fileOrgName, fileSaveName, fileDir, "
				+ "fileSize, fileType FROM house_image "
				+ "WHERE house_seq=%d;";
		
		ResultSet rs2 = db.getResult(fileSql, seq);
		
		if (rs2 == null) {
			return null;
		}
		
		ArrayList<FileInfo> imageList = new ArrayList<FileInfo>();
		
		// 반복문을 처리하면서 파일목록 생성
		try {
			while (rs2.next()) {
				String fileOrgName = rs2.getString(1);
				String fileSaveName = rs2.getString(2);
				String fileDir = rs2.getString(3);
				int fileSize = rs2.getInt(4);
				String fileType = rs2.getString(5);
				
				FileInfo info = new FileInfo("", fileOrgName, fileSaveName, fileDir, fileSize, fileType);
				imageList.add(info);
			}
		} catch (SQLException e) {
			// 에러 발생시 에러 정보의 로그 기록
			logger.error(e.getLocalizedMessage());
			logger.error(e.getStackTrace());
		} finally {
			try {
				rs2.close();
			} catch (SQLException e) {}
			rs2 = null;
		}
		return imageList;
	}
}